import re
import pymysql.cursors
from datetime import datetime
import json
import sys

# -*- coding: utf-8 -*-
# python 3.5



class MySQL:
    # def __init__(self,host=None,user=None,pwd=None,db=None):
    def __init__(self):
        self.host = '127.0.0.1'
        self.user = 'root'
        self.pwd = '1998818'
        self.db = 'PythonWeb'

        self._conn = self.GetConnect()
        if (self._conn):
            self._cur = self._conn.cursor()

    # 连接数据库
    def GetConnect(self):
        conn = False
        try:
            conn = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.pwd,
                database=self.db
            )
        except Exception as err:
            print("连接数据库失败, %s" % err)
        else:
            return conn

    # 执行查询
    def ExecQuery(self, sql):
        res = ""
        try:
            self._cur.execute(sql)
            res = self._cur.fetchall()
        except Exception as err:
            print("查询失败, %s" % err)
        else:
            return res

    # 执行非查询类语句
    def ExecNonQuery(self, sql):
        flag = False
        try:
            self._cur.execute(sql)
            self._conn.commit()
            flag = True
        except Exception as err:
            flag = False
            self._conn.rollback()
            print("执行失败, %s" % err)
        else:
            return flag

    # 获取连接信息
    def GetConnectInfo(self):
        print("连接信息：")
        print("服务器:%s , 用户名:%s , 数据库:%s " % (self.host, self.user, self.db))

    # 关闭数据库连接
    def Close(self):
        if (self._conn):
            try:
                if (type(self._cur) == 'object'):
                    self._cur.close()
                if (type(self._conn) == 'object'):
                    self._conn.close()
            except:
                raise ("关闭异常, %s,%s" % (type(self._cur), type(self._conn)))


def sql():
    db = MySQL()
    # 获取分类链接
    sql = "select user_star, count(*) from blog_user group by user_star"
    db.GetConnectInfo()
    star = db.ExecQuery(sql)
    print(type(star), star)
    # row = star.fetchone()
    # result = {}
    # result['category'] = row[0]
    # result['number'] = str(row[1])
    # jsonStr = json.dumps(result, ensure_ascii=False)
    # print(result, jsonStr)

def dict():
    List = ['自强学堂', '渲染Json到模板']
    Dict = {'site': '自强学堂', 'author': '涂伟忠'}
    print(List, json.dumps(List, ensure_ascii=False))
    print(Dict, json.dumps(Dict, ensure_ascii=False))


def sqls():
    # 连接数据库
    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='1998818',
        db='PythonWeb',
        charset='utf8'
    )
    # 获取游标
    cursor = connect.cursor()
    # 获取分类链接
    sql = "select blog_category, count(*) from blog_blog group by blog_category"
    cursor.execute(sql)
    connect.commit()
    categorys = cursor.fetchall()
    print(categorys)
    jsonDate = []
    for row in categorys:
        result = {}
        result['category'] = row[0]
        result['number'] = str(row[1])
        jsonDate.append(result)
    jsonStr = json.dumps(jsonDate, ensure_ascii=False)
    print(jsonStr)
    array = json.loads(jsonStr)
    print(array)


if __name__ == '__main__':
    sql()
    # dict()
# sql = """
#     update blog_user
#     set user_sex = '保密', user_age = '保密', user_hometown = '保密', user_birth = '0000-00-00', user_aim = '保密', user_status = '保密', user_star = '保密', user_blood = '保密', user_height = '160', user_shape = '保密'
#     where user_id = '%d'"""
# date = (user[0])
# cursor.execute(sql % date)
# connect.commit()
# print('修改成功')